package com.bw.dao;

import com.bw.bean.AxIn;
import com.bw.bean.Cx;
import com.bw.bean.Stt;
import com.bw.bean.Xxk;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SttDao {
    @Select("select s.sid,s.sname,count(c.cid) counts,sum(c.score) sums from student s\n" +
            "LEFT JOIN sc c ON c.sid=s.sid \n" +
            "LEFT JOIN course e ON c.cid=e.cid\n" +
            "group by s.sname")
    List<Stt> getList();

    @Select("select max(score) max,MIN(score) min from sc GROUP BY sid")
    List<AxIn> getList1();

    @Select("select t.*,c.cid,c.cname,AVG(sc.score) avg from course c\n" +
            "LEFT JOIN sc sc ON c.cid=sc.cid\n" +
            "LEFT JOIN teacher t ON c.tid=t.tid\n" +
            "group by c.cname order by avg desc")
    List<Cx> getList2();

    @Select("select c.cid,c.cname from course c,sc s where c.cid=s.cid group by c.cid and s.sid in(select sid from student)")
    List<Xxk> getList3();
}
